package com.share.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;

import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @desc:
 * @author:caifan
 * @date:2021/3/20
 */
public class ExcelRead {

    public static void main(String[] args) throws Exception {
        File file = new File("D:/test/yao/【生意参谋平台】无线店铺流量来源-2021-03-05_2021-03-05.xls");
        ExcelReaderBuilder read = EasyExcel.read(file);
        List<LinkedHashMap> objects = read.doReadAllSync();
        System.out.println(objects.size());
        ExcelRead excelRead = new ExcelRead();
        List<ExcelEntity> dataFromExcel = excelRead.getDataFromExcel();
        System.out.println(dataFromExcel);
        List<String> header = Arrays.asList("时间", "来源明细", "访客数", "支付买家数", "支付金额");
        EasyExcel.write("D:/test/yao/无线流量汇总" + ".xls")

//                .head(header)
                .sheet("无线流量").doWrite(dataFromExcel);
    }


    private List<ExcelEntity> getDataFromExcel() throws Exception {
        File dirFile = new File("D:/test/yao");
        File[] files = dirFile.listFiles();
        List<ExcelEntity> excelEntityAllList = new ArrayList<>();
        for (File file : files) {
            ExcelReaderBuilder read = EasyExcel.read(file);
            List<LinkedHashMap> dataMapList = read.doReadAllSync();
            List<ExcelEntity> excelEntityList = map2Obj(dataMapList, file.getName());
            excelEntityAllList.addAll(excelEntityList);
        }
        excelEntityAllList = excelEntityAllList.stream().filter(entity -> Objects.nonNull(entity.getSource())).collect(Collectors.toList());
        return excelEntityAllList;
    }

    /**
     *  转excel数据为对象
     * @param mapList
     * @param fileName 读取日期
     * @return
     */
    private List<ExcelEntity> map2Obj(List<LinkedHashMap> mapList, String fileName) throws Exception {
        String dateStr = fileName.substring(fileName.indexOf("_") + 1, fileName.lastIndexOf("."));
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = dateFormat.parse(dateStr);
        List<ExcelEntity> excelEntityList = new ArrayList<>();
        for (LinkedHashMap map : mapList) {
            ExcelEntity entity = new ExcelEntity();
            if (map.size() == 29) {
                if (map.get(1).toString().equals("流量来源") || Objects.isNull(map.get(2)) || map.get(2).toString().equals("汇总")) {
                    continue;
                }
                entity.setSource(map.get(2).toString());
                Integer visitor = Integer.parseInt(map.get(3).toString());
                entity.setVistorCount(visitor);
                Integer payCount = (Integer.parseInt(map.get(13).toString()));
                if (visitor.equals(0)) {
                    continue;
                }
                entity.setPayerCount(payCount);
                String priceValue = map.get(11).toString();
                priceValue = priceValue.trim().replace(",", "");
                entity.setPrice(BigDecimal.valueOf(Double.valueOf(priceValue)));

            } else if (map.size() == 28) {
                if (map.get(0).toString().equals("流量来源") || Objects.isNull(map.get(1)) || map.get(1).toString().equals("汇总")) {
                    continue;
                }
                entity.setSource(map.get(1).toString());
                Integer visitor = Integer.parseInt(map.get(2).toString());
                entity.setVistorCount(visitor);
                Integer payCount = Integer.parseInt(map.get(12).toString());
                if (visitor.equals(0)) {
                    continue;
                }
                entity.setPayerCount(payCount);
                String priceValue = map.get(10).toString();
                priceValue = priceValue.trim().replace(",", "");
                entity.setPrice(BigDecimal.valueOf(Double.valueOf(priceValue)));
            }
            entity.setDate(date);
            excelEntityList.add(entity);
        }
        return excelEntityList;
    }

}
